ODBC - Open Database Connectivity -- Connecting the PC to data sources

by John Sarabacha

So, what is ODBC?

ODBC stands for open database connectivity. Essentially, ODBC is an application program interface that allows PC client applications running under Microsoft Windows 3.1 or Windows NT to access data from a variety of existing data sources. These data sources may reside on the client machine or they may be located on a remote server connected by an appropriate network that is common to the client machine. Data sources may vary in complexity from simple user-made or shrink-wrapped databases (e.g., Excel) running under Windows on the client machine to more sophisticated and powerful relational database management systems (RDBMS) running on a Unix server and using TCP/IP networking.

For a PC client application to be able to access data from a data source, a driver DLL (Dynamic Link Library) must exist for each data source to be accessed. Theoretically, all PC client applications that have already been written using the ODBC API Standard can access any data source, provided that a driver exists to support that data source.

Essentially, only the driver knows how to talk to its matching data source.

The challenges

To access a particular data source, a PC client driver must exist to support that data source; if it doesn't exist, then it must be written. This driver must be in compliance with the ODBC API Standard in order for existing or future PC client applications to be able to access that data source.

The driver must support the following minimum requirements:

  1. Establish a connection to the data source over an appropriate network (for remote data sources);
  2. process client application SQL function requests (through the ODBC API);
  3. submit the SQL requests to the data source;
  4. handle the appropriate data conversions between the client application and data source;
  5. return the results of the SQL request back to the application;
  6. declare and manipulate cursors if necessary; and
  7. initiate transactions (commit and rollback operations).

Let's look at these requirements in more detail.

  1. Establish a connection to the data source over an appropriate network (remote data sources). The PC client driver must be able to make calls into a networking library to support communications to a remote server platform. For instance, for Empress Software's PC Driver, a TCP/IP networking library (WINSOCK) was used to communicate with a remote Unix system running an Empress ODBC Server. This library was chosen because it conforms to the open networking standard, Windows Sockets API. Calls to this API allow the driver to run over any vendor's TCP/IP protocol stack and Windows Sockets DLL that complies with the standard.
  2. Processes client application SQL function requests (through the ODBC API). The PC driver requires that its software be written in conformance with the ODBC API Standard. The conformance levels are classified as:

    (i) Core API
    (ii) Level 1 API
    (iii) Level 2 API

    The Core API functions are based on the X/Open and SQL Access Group Call Level interface specification. Level 1 & Level 2 API are based on the Core API function specification, with additional support for such features as scrollable cursors and asynchronous processing.

  3. Submit the SQL requests to the data source. The PC driver requires programming support for a high-level communication protocol that best supports sending SQL requests to a local or remote data source.
  4. Handle the appropriate data conversions between the application and data source. The data stored at the data source has data types in a format specific to the source. In some cases the data source can be heterogeneous. This can be handled by converting the machine-specific data type to a generic type which can be converted by the PC driver to the type required by the client application.
  5. Return the results of the SQL request back to the application. Using the same high-level communication protocol described above in (3), after receiving PC driver requests and executing them, the data source sends the PC driver the results of the request (e.g., requested data). The driver would then follow the data conversion rules mentioned in (4) before forwarding the data to the PC client application.
  6. Declares and manipulates cursors if necessary. The PC driver can use cursors to keep track of its position in the result set. Each time an SQL fetch request is issued from the client application, the driver can issue a fetch request to the data source to move the cursor to the next row and receive that row from the data source. The driver allows cursor names to be used so that client applications can request positioned update and delete operations.
  7. Can initiate transactions (commit and rollback operations). The driver supports two transaction modes: auto-commit and manual-commit. In auto-commit mode, the driver executes each client application SQL request within a transaction. In manual-commit mode, the driver begins a transaction when a client application submits an SQL request and no other transaction is currently open, and then commits or rolls back the current transaction by an ODBC API function call. The client application can control whether the driver will work in auto-commit or manual-commit mode.

Conclusion

There are many challenges involved in writing a PC client driver that can talk to a data source. In addition to the points covered above, there are many subtle issues not evident at design time that manifest themselves during implementation and testing. Some of these issues involve the interpretation and limitations of the ODBC specification when designing ODBC drivers and ODBC client applications. Others include resolving more subtle driver requirements in order to support popular client applications like Excel. Different client applications have different driver requirements; this is not supposed to happen, but it does.

While ODBC isn't the total solution for true open data base connectivity, it is a partial solution and a starting point.

John Sarabacha is a software developer for Empress Software Inc. He can be reached at jsara@empress.com.